<?php
class Customer_model extends CI_Model {

    public function __construct()
    {
        $this->load->database();
    }
    public function get_customer_details($idcustomer)
    {
    
        $query = $this->db->query('SELECT * FROM 
                                    customer WHERE idcustomer = '.$idcustomer.' LIMIT 1
                                    ');
        return $query->result();
    }
    public function get_customer_name ($idcustomer)
    {
        $query = $this->db->query(' SELECT CONCAT (title.title, " ", customer.name) as name
                                    FROM customer join title  
                                    ON customer.idcustomer = '.$idcustomer.' 
                                    WHERE title.idtitle = customer.title_idtitle;');
        $row = $query->row();
        return $row->name;
    }
    public function get_customer_id_using_job_number ($job_number){
        $query = $this->db->query(' SELECT idcustomer 
                                    FROM customer 
                                    WHERE idcustomer IN (SELECT customer_idcustomer from job where idjob = '.$job_number.')');
        $row = $query->row();
        return $row->idcustomer;
    }
    public function get_customer_address_using_customer_id($customer_id){
        $query = $this->db->query('     SELECT * from address, address_type    
                                        WHERE 
                                        address_type.idaddress_type = address.address_type_idaddress_type
                                        AND
                                        address.idaddress in (SELECT address_has_customer.address_idaddress
                                        FROM address_has_customer
                                        WHERE address_has_customer.customer_idcustomer = '.$customer_id.')
                                        ');   
        return $query->result();
    }
    public function search_customer($string){
    	$this->db->query('SET @string = CONCAT("%", LOWER("'.$string.'"),"%");');
        $query = $this->db->query(' 
 

SELECT address_has_customer.idaddress_has_customer as ID, 
	customer.idcustomer as cust_id, 
	CONCAT(title.title, " ", customer.name) as name, 
	customer.phone as phone,
	CONCAT(field1," ", field2, " " ,postcode) as address
FROM address, address_has_customer, customer, title, address_type
WHERE            
	(address.idaddress = address_has_customer.address_idaddress)
	AND (customer.idcustomer = address_has_customer.customer_idcustomer)
	AND (title.idtitle = customer.title_idtitle)
	AND (address.address_type_idaddress_type = address_type.idaddress_type)
	AND ((LOWER(name) like (@string))
	OR (LOWER(address.idaddress) like (@string))
	OR (LOWER(address.field1) like (@string))
	OR (LOWER(address.field2) like (@string))
	OR (LOWER(address.postcode) like (@string))
	OR (LOWER(customer.idcustomer) like (@string))
	OR (LOWER(customer.phone) like (@string)))
 ');
        return $query->result_array();
    }
	public function search_business_customer($string){
				$this->db->query('SET @string = CONCAT("%", LOWER("'.$string.'"),"%")');
		        $query = $this->db->query('
    SELECT address_has_customer.idaddress_has_customer as ID, 
	customer.idcustomer as cust_id, 
	CONCAT(title.title, " ", customer.name) as name, 
	customer.phone as phone,
	CONCAT(field1," ", field2, " " ,postcode) as address,
   business_name.name as business_name 
FROM address, address_has_customer, customer, title, address_type, business_name
WHERE            
	(address.idaddress = address_has_customer.address_idaddress)
	AND (customer.idcustomer = address_has_customer.customer_idcustomer)
	AND (title.idtitle = customer.title_idtitle)
	AND (address.address_type_idaddress_type = address_type.idaddress_type)
    AND (address.business_name_idbusiness_name=business_name.idbusiness_name)
	AND ((LOWER(customer.name) like (@string))
	OR (LOWER(address.idaddress) like (@string))
	OR (LOWER(address.field1) like (@string))
	OR (LOWER(address.field2) like (@string))
	OR (LOWER(address.postcode) like (@string))
	OR (LOWER(customer.idcustomer) like (@string))
	OR (LOWER(customer.phone) like (@string))
    OR(LOWER(business_name.name) like (@string)))

                                    ');
        return $query->result_array();
	}
    public function get_all_customers(){
        $query = $this->db->query('
                                    SELECT  CONCAT("<a href = \"#\">", idcustomer, "</a>") as ID, 
                                            CONCAT(title.title, " ", customer.name) as name, 
                                            customer.phone, 
                                            CONCAT(field1," ", field2, " " ,postcode) as address,
                                            address_type.type 
                                    FROM address, address_has_customer, customer, title, address_type
                                    WHERE 
                                    ((address.idaddress = address_has_customer.address_idaddress)
                                    AND (address_has_customer.customer_idcustomer = customer.idcustomer))
                                    AND (title.idtitle = customer.title_idtitle)
                                    AND (address_type.idaddress_type = address.address_type_idaddress_type)
                                    ;');
        return $query->result_array();
    }
	public function get_title_list(){
		$query = $this->db->query('
									SELECT * FROM title; 
									');
		//$query->title = 				
		return $query->result();
			
	}
	public function get_business_list(){
		$query = $this->db->query('
								SELECT * FROM business_name
		');
		return $query ->result();
	}
	public function insert_customer($title, $name, $phone, $email, $field1, $field2, $postcode){
		if ($this->db->query('
			INSERT INTO 
			customer (name, phone, title_idtitle, email) 
			VALUES ("'.$name.'", "'.$phone.'",'.$title.',"'.$email.'");')){
				if ($this->db->query('
					set @cust = LAST_INSERT_ID();')){
						if ($this->db->query('
							INSERT INTO 
							address (field1, field2, postcode, address_type_idaddress_type, business_name_idbusiness_name)
							VALUES ("'.$field1.'", "'.$field2.'", "'.$postcode.'", 1, 1);')){
								if ($this->db->query('set @address = LAST_INSERT_ID();')){
									if ($this->db->query('
										INSERT INTO 
										address_has_customer (address_idaddress, customer_idcustomer)
										VALUES (@address, @cust);')){
											return  $this->db->insert_id();
										}
									}
						}
				}
		}else{
			return FALSE;
		}
	}
	public function insert_business_customer($title, $name, $phone, $email, $business){
		/*if ($this->db->query('
			INSERT INTO 
			customer (name, phone, title_idtitle, business_name_idbusiness_name, email) 
			VALUES ("'.$name.'", "'.$phone.'", "'.$title.'", "'.$business.'", "'.$email.'");')){
				return TRUE;
			}else{
				return FALSE;
			} 0
		 * 
		 */
		if ($this->db->query('
			INSERT INTO 
			customer (name, phone, title_idtitle, email) 
			VALUES ("'.$name.'", "'.$phone.'",'.$title.',"'.$email.'");')){
				if ($this->db->query('
					set @cust = LAST_INSERT_ID();')){
						if ($this->db->query('
							set @var = (select address.idaddress 
							From address, business_name
							where
							business_name.idbusiness_name = address.business_name_idbusiness_name
							AND business_name.idbusiness_name = '.$business.')')){
								if ($this->db->query('
									INSERT INTO 
									address_has_customer (address_idaddress, customer_idcustomer)
									VALUES (@var, @cust);')){
										return  $this->db->insert_id();
									}
						}
			
				}
		}else{
			return FALSE;
		}	
			
	}

}
























;